﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Wicresoft.Common;
using System.Data;
using System.Text;

public partial class Page_AttendenceStat : BaseAuthenticationPage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }
    private DataTable BindData()
    {
        string where = "1=1";
        if (!string.IsNullOrEmpty(txtStartDate.Text))
            where += string.Format(" AND AttendDate >= '{0} 00:00:00'", txtStartDate.Text);
        if (!string.IsNullOrEmpty(txtEndDate.Text))
            where += string.Format(" AND AttendDate <= '{0} 23:59:59'", txtEndDate.Text);

        where += " AND Remark in ( '100','200','400','500','600')";


        if (rpType2.SelectedValue == "大厅值班")
        {
            where += " AND Remark = '300'";
        }
        string sql = string.Format(
                      @"SELECT UserName,
       UserDisplayName,
       Sum(CASE
             WHEN ( IsWeekEnd = 1  )
                  AND Datediff(DAY, attendtimestart, AttendTimeEnd) = 0
                  AND Datepart(hour, attendtimestart) >= 12
                  AND Datepart(hour, attendtimeEnd) <= 17 THEN 0.5
             WHEN ( IsWeekEnd = 1)
                  AND Datediff(DAY, attendtimestart, AttendTimeEnd) = 0
                  AND Datepart(hour, attendtimestart) >= 8
                  AND Datepart(hour, attendtimeEnd) <= 12 THEN 0.5
             WHEN ( IsWeekEnd = 1 )
                  AND Datediff(DAY, attendtimestart, AttendTimeEnd) = 0
                  AND Datepart(hour, attendtimestart) >= 8
                  AND Datepart(hour, attendtimeEnd) <= 17 THEN 1
             ELSE 0
           END) AS 'DayCount',
       Sum(CASE
             WHEN ( isholiday = 0 )
                  AND Datediff(DAY, attendtimestart, AttendTimeEnd) = 1
                  AND ( ( Datepart(hour, attendtimestart) >= 18
                          AND Datepart(hour, attendtimeEnd) <= 24 )
                         OR ( Datepart(hour, attendtimestart) >= 0
                              AND Datepart(hour, attendtimeEnd) <= 8 ) ) THEN 1
             ELSE 0
           END) AS 'NightCount',
       Sum(CASE
             WHEN ( isholiday = 1 )
                  AND Datediff(DAY, attendtimestart, AttendTimeEnd) = 0
                  AND Datepart(hour, attendtimestart) >= 8
                  AND Datepart(hour, attendtimeEnd) <= 12 THEN 0.5
             WHEN ( isholiday = 1 )
                  AND Datediff(DAY, attendtimestart, AttendTimeEnd) = 0
                  AND Datepart(hour, attendtimestart) >= 12
                  AND Datepart(hour, attendtimeEnd) <= 17 THEN 0.5
             WHEN ( isholiday = 1 )
                  AND Datediff(DAY, attendtimestart, AttendTimeEnd) = 0
                  AND Datepart(hour, attendtimestart) >= 8
                  AND Datepart(hour, attendtimeEnd) <= 17 THEN 1
             ELSE 0
           END) AS 'JJRDayCount',
       Sum(CASE
             WHEN ( isholiday = 1 )
                  AND Datediff(DAY, attendtimestart, AttendTimeEnd) = 1
                  AND ( ( Datepart(hour, attendtimestart) >= 18
                          AND Datepart(hour, attendtimeEnd) <= 24 )
                         OR ( Datepart(hour, attendtimestart) >= 0
                              AND Datepart(hour, attendtimeEnd) <= 8 ) ) THEN 1
             ELSE 0
           END) AS 'JJRNightCount'
FROM   View_Attend_All
WHERE {0}   group by  UserName,UserDisplayName", where);
        DataTable dt = DataAccess.ExecuteSql("Attendences.ConnectionString", sql).Tables[0];
        rptInfo.DataSource = dt;
        rptInfo.DataBind();
        return dt;
    }
    protected void lbtnExport_Click(object sender, EventArgs e)
    {
        DataTable dt = BindData();
        string filename = "值班明细统计";
        if (txtStartDate.Text != string.Empty)
        {
            filename += Convert.ToDateTime(txtStartDate.Text).ToString("(yyyy年MM月dd日");
        }
        else
        {
            filename += "";
        }
        if (txtEndDate.Text != string.Empty)
        {
            filename += "-" + Convert.ToDateTime(txtEndDate.Text).ToString("yyyy年MM月dd日)");
        }
        else
        {
            filename += "";
        }
        Response.Clear();
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(filename) + ".xls");
        Response.ContentType = "application/ms-excel";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");

        StringBuilder sw = new StringBuilder();
        sw.Append("<table border='1' style='border-collapse:collapse'>");
        string tableHeader = @"<tr class=""Normal"">
                                    <td width=""120"" align=""center"" rowspan=""2"">
                                        序号
                                    </td>
                                    <td width=""120"" align=""center"" rowspan=""2"">
                                        姓名
                                    </td>
                                    <td rowspan=""2"" width=""120"">
                                        夜间值班
                                    </td>
                                    <td rowspan=""2"" width=""120"">
                                        白天值班
                                    </td>
                                    <td colspan=""2"" align=""center"">
                                        节假日值班
                                    </td>
                                </tr>
                                <tr class=""Normal"">
                                    <td width=""120"">
                                        白天
                                    </td>
                                    <td width=""120"">
                                        夜晚
                                    </td>
                                </tr>";
        sw.Append(tableHeader);
        int i = 1;
        foreach (DataRow dr in dt.Rows)
        {
            sw.Append(string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td></tr>",
                                         i,
                                         dr["UserDisplayName"],
                                         dr["NightCount"],
                                         dr["DayCount"],
                                         dr["JJRDayCount"],
                                         dr["JJRNightCount"]));
            i += 1;
        }
        sw.Append("</table>");


        byte[] bs = System.Text.Encoding.GetEncoding("utf-8").GetBytes(sw.ToString());

        byte[] outer = new byte[bs.Length + 3];
        outer[0] = (byte)0xef;
        outer[1] = (byte)0xbb;
        outer[2] = (byte)0xbf;
        Array.Copy(bs, 0, outer, 3, bs.Length);
        Response.BinaryWrite(outer);
        Response.Flush();
        Response.End();
    }
    protected void lbtnSearch_Click(object sender, EventArgs e)
    {
        BindData();
    }
}